/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.ssm.changeunit.persistance;

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

/**
 *
 * @author EuiHyun
 */
public class ChildDAO extends AbstractChildDAO {

    @Override
    public ChildDTO find(String childId) throws PersistanceException {
        ChildDTO dto = null;
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select CHILDID, CHANGEUNITID, CHILDCHANGEUNITID from APP.CHILD where CHILDID = '"+childId+"'";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            if(rs.next()) {
                dto = new ChildDTO();
                
                dto.setChildId(rs.getString("CHILDID"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));
                dto.setChildChangeUnitId(rs.getString("CHILDCHANGEUNITID"));
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ChildDAO.find: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dto; 
    }

    @Override
    public List<ChildDTO> findByChangeUnitId(String changeUnitId) throws PersistanceException {
        
        List<ChildDTO> dtoArray = new ArrayList<ChildDTO>();
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
      String sql="select CHILDID, CHANGEUNITID, CHILDCHANGEUNITID from APP.CHILD where CHANGEUNITID = '"+changeUnitId+"'";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            while(rs.next()){
                ChildDTO dto = new ChildDTO();
                dto.setChildId(rs.getString("CHILDID"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));
                dto.setChildChangeUnitId(rs.getString("CHILDCHANGEUNITID"));

                dtoArray.add(dto);
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ChildDAO.findByChangeUnitId: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dtoArray; 
    }
    @Override
    public List<ChildDTO> list() throws PersistanceException {
        
        List<ChildDTO> dtoArray = new ArrayList<ChildDTO>();
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
      String sql="select CHILDID, CHANGEUNITID, CHILDCHANGEUNITID from APP.CHILD ";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            while(rs.next()){
                ChildDTO dto = new ChildDTO();
                dto.setChildId(rs.getString("CHILDID"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));
                dto.setChildChangeUnitId(rs.getString("CHILDCHANGEUNITID"));

                dtoArray.add(dto);
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ChildDAO.list: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dtoArray; 
    }

    @Override
    public void insert(ChildDTO childDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
//        String sql="insert into APP.CHILD (CHILDID, CHANGEUNITID, CHILDCHANGEUNITID ) " 
//                    + "values ('"+childDTO.getChildId()+"','"+ childDTO.getChangeUnitId() +"','"+ childDTO.getChildChangeUnitId() +"')";
        
        String sql="insert into APP.CHILD (CHILDID, CHANGEUNITID, CHILDCHANGEUNITID ) " 
                    + "values (?,?,?)";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql); 
            
            stmt.setString(1, childDTO.getChildId());
            stmt.setString(2, childDTO.getChangeUnitId());
            stmt.setString(3, childDTO.getChildChangeUnitId());
            
            stmt.executeUpdate(); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChildDAO.insert: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }
    }

    @Override
    public void update(ChildDTO childDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
//        String sql="update APP.CHILD set CHILDID= '"+ childDTO.getChildId()
//                + "' ,CHANGEUNITID= '"+ childDTO.getChangeUnitId()
//                + "' ,CHILDCHANGEUNITID= '"+ childDTO.getChildChangeUnitId()
//                + "' where CHILDID = '"+ childDTO.getChildId() +"'";
        
        String sql="update APP.CHILD set CHILDID=? ,"
                + " CHANGEUNITID=? ,"
                + " CHILDCHANGEUNITID=? "
                + " where CHILDID=? ";
        
        try { 
            conn = dm.getConnection(); 
            stmt = conn.prepareStatement(sql); 
            
            stmt.setString(1, childDTO.getChildId());
            stmt.setString(2, childDTO.getChangeUnitId());
            stmt.setString(3, childDTO.getChildChangeUnitId());
            stmt.setString(4, childDTO.getChildId());
            
            stmt.executeUpdate(); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChildDAO.update: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }   
    }

    @Override
    public void delete(String childId) throws PersistanceException {
        Connection conn = null; 
        Statement stmt = null;  
        
        //Create SQL Statement
        /*
         * TO-DO check where cluase 
         */
        String sql="delete from APP.CHILD "  
                   +"WHERE CHILDID = '"+childId+"'";  
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            stmt.executeUpdate(sql); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChildDAO.delete: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
    }

    @Override
    public void deleteByChangeUnitId(String changeUnitId) throws PersistanceException {
        Connection conn = null; 
        Statement stmt = null;  
        
        //Create SQL Statement

        String sql="delete from APP.CHILD "  
                   +"WHERE CHANGEUNITID = '"+changeUnitId+"'";  
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            stmt.executeUpdate(sql); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChildDAO.deleteByChangeUnitId: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
    }

}
